package com.czlis.boe;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.hutool.core.io.FileUtil;

public class ReadClobSysLog {

	public static String BOX_NAMES = "SX-491903";
	public static String url = "jdbc:oracle:thin:@118.178.86.19:1521/orcl";
	public static String username = "czlis";
	public static String password = "junaiTAO1!";
	public static List<String> FAIL_LIST = new ArrayList<>();
	public static List<String> NORMAL_LIST = new ArrayList<>();

	public static void main(String[] args) {
		int index = 1;
		updateBoxName();
		FAIL_LIST.add("没有成功日志的箱号：");
		for(String boxName : BOX_NAMES.split(",")) {
			String readBox = "Read boxname：" + boxName + " 序号：" + (index++);
			addNormalList(readBox);
			try (Connection connection = DriverManager.getConnection(url, username, password);
					PreparedStatement statement = connection.prepareStatement(
							"select oper, l.oper_data_all,l.info,l.opertime from sys_log l where l.oper like '上传天马深圳%' and l.info like '%成功箱号%"+boxName+"%' and l.operdate>='20240901'");
					ResultSet resultSet = statement.executeQuery()) {
				Boolean hasLog = false;
				while (resultSet.next()) {
					hasLog = true;
					String oper = resultSet.getString("oper");
					String info = resultSet.getString("info");
					addNormalList(oper + "  " + info + " " + resultSet.getString("opertime"));
					java.sql.Clob clob = resultSet.getClob("oper_data_all");
					if (clob != null) {
						String clobContent = clob.getSubString(1, (int) clob.length());
						// System.out.println("CLOB content: " + clobContent);
						String flag = "";
						if(oper.contains("黑名单")) {
							flag = "_黑名单";
						}
						FileUtil.writeString(clobContent, new File("D:\\AijunTao\\Desktop\\深圳数据\\" + boxName + flag + ".json"), "UTF-8");
						break;
					}
				}
				
				if(!hasLog) {
					FAIL_LIST.add(boxName);
				}
	
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		System.out.println("没有成功日志的箱号：");
		for(String bn : FAIL_LIST) {
			System.out.print(bn + ", ");
		}
		FileUtil.writeLines(NORMAL_LIST, new File("D:\\AijunTao\\Desktop\\boxLog.txt"), "UTF-8");
		FileUtil.writeLines(FAIL_LIST, new File("D:\\AijunTao\\Desktop\\boxLog.txt"), "UTF-8", true);
	}


	private static void addNormalList(String readBox) {
		NORMAL_LIST.add(readBox);
		System.out.println(readBox);
	}

	private static void updateBoxName() {
		BOX_NAMES = "";
		try (Connection connection = DriverManager.getConnection(url, username, password);
				PreparedStatement statement = connection.prepareStatement(
						"""
						select boxname from ct_slimifretbox b where b.destination='3' and b.targetproductspecname like 'TC172XYKS51-00C%' and b.targetproductrequest='I13EW22410007' 
						union all
						select boxname from ct_slimifretbox b where b.destination='3' and b.targetproductspecname like 'TC172XYKS52-00C%' and b.targetproductrequest='I13EW22410008' 
						union all
						select boxname from ct_slimifretbox b where b.destination='3' and b.targetproductspecname like 'TC172XYKS53-00C%' and b.targetproductrequest='I13EW22410009'
						
						"""
						);
				ResultSet resultSet = statement.executeQuery()) {

			while (resultSet.next()) {
				String boxname = resultSet.getString("boxname");
				BOX_NAMES += boxname + ",";
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		
	}

}
